lyfz_repos/铂金版源码/DBFile/SQL/2015-08-13[收集]/刘工修改后的订单报表.sql

USE [DYBBERPDB]
GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_OrderReport]'))
DROP VIEW [dbo].[BView_OrderReport]
GO
CREATE VIEW [dbo].[BView_OrderReport]
AS
SELECT tb_ErpOrder.ID,dbo.tb_ErpOrder.Ord_DividedShop, dbo.tb_ErpOrder.Ord_Number, tb_ErpOrderDigital.Ordv_ViceNumber, 
dbo.tb_ErpOrder.Ord_Class, Ord_Type, Ord_OrderClass,Ordv_DigitalNumber,
CASE Ord_SinceOrderNumber WHEN '' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号, 
dbo.fn_CheckOrderType(dbo.tb_ErpOrder.Ord_Type) AS 订单类型, 

Cus_Name AS 客户姓名,
Cus_Name_py AS 客户拼音,
Cus_Sex_cs AS 客户性别,
Cus_Telephone AS 客户电话,
[Age_String] AS 年龄,
[Age_Day]  AS 天,
[Age_Year] AS 年,

dbo.tb_ErpOrder.Ord_PhotographyCategory AS 套系类别, 
dbo.tb_ErpOrder.Ord_CustomerSource AS 客户来源, 
dbo.tb_ErpOrder.Ord_SeriesName AS 套系名称, 
dbo.tb_ErpOrder.Ord_SeriesPrice AS 套系价格, 
(select stuff((select ',' + dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber = Ord_Number for xml path('')),1,1,'')) as 接单人,
dbo.fn_ChineseToSpell((select stuff((select ','+dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber = Ord_Number for xml path('')),1,1,''))) as 接单人拼音,
(case when Ord_Type = '1' then (select Ordpg_Sights from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber) else '' end) as 拍摄名称,
(select stuff((select ',' + dbo.fn_CheckUserIDGetUserName(Ordpg_Photographer) from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber and Ordpg_Photographer!= '' group by Ordpg_Photographer for xml path('')),1,1,'')) as 摄影师,
(select stuff((select ',' + dbo.fn_CheckUserIDGetUserName(Ordpg_MakeupArtist) from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber and Ordpg_MakeupArtist!= '' group by Ordpg_MakeupArtist for xml path('')),1,1,'')) as 化妆师,
(select top 1 dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(Ordpg_PhotographyTime)) from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber and Ordpg_PhotographyTime is not null order by Ordpg_PhotographyTime DESC) AS 拍摄时间, 
(case when (select Count(Ordpg_PhotographyStatus) from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber and Ordpg_PhotographyStatus = '2') > 0 then '拍摄中' 
else case when (select Count(Ordpg_PhotographyStatus) from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber and Ordpg_PhotographyStatus = '1') > 0 then 'OK' else '未拍' end end) AS 拍照状态,
dbo.fn_CheckUserIDGetUserName(tb_ErpOrderDigital.Ordv_EarlyRepairName) AS 初修师, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(Ordv_EarlyRepairTime)) AS 初修时间, 
dbo.fn_CheckOrderEarlyRepairStatus(tb_ErpOrderDigital.Ordv_EarlyRepairStatus) AS 初修状态, 
dbo.fn_CheckUserIDGetUserName(tb_ErpOrderDigital.Ordv_FilmSelectionName) AS 选片师, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(Ordv_FilmSelectionTime)) AS 选片时间, 
dbo.fn_CheckOrderFilmSelectionStatus(tb_ErpOrderDigital.Ordv_FilmSelectionStatus) AS 选片状态, 
dbo.fn_CheckUserIDGetUserName(tb_ErpOrderDigital.Ordv_DesignerName) AS 设计师, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(tb_ErpOrderDigital.Ordv_DesignerTime)) AS 设计时间, 
dbo.fn_CheckOrderDesignerStatus(tb_ErpOrderDigital.Ordv_DesignerStatus) AS 设计状态, 
dbo.fn_CheckUserIDGetUserName(tb_ErpOrderDigital.Ordv_RefinementName) AS 精修师, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(tb_ErpOrderDigital.Ordv_RefinementTime)) AS 精修时间, 
dbo.fn_CheckOrderRefinementStatus(tb_ErpOrderDigital.Ordv_RefinementStatus) AS 精修状态, 
dbo.fn_CheckUserIDGetUserName(tb_ErpOrderDigital.Ordv_LookDesignName) AS 看设计师, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(tb_ErpOrderDigital.Ordv_LookDesignTime)) AS 看设计时间, 
dbo.fn_CheckOrderLookDesignStatus(tb_ErpOrderDigital.Ordv_LookDesignStatus) AS 看设计状态, 
tb_ErpOrderDigital.Ordv_LookDesignClaim AS 看设计要求, 
[dbo].[fun_GetPickupStatusStatus]([dbo].[Vw_OrderProductPickupView].[OPlist_PickupStatus],[dbo].[Vw_OrderProductPickupView].productCount) AS 取件状态,
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime([dbo].[Vw_OrderProductPickupView].OPlist_PickupTime)) AS 取件日期, 
dbo.tb_ErpOrder.Ord_Remark AS 备注, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrder.Ord_CreateName) AS 录入员, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(Ord_CreateDateTime)) AS 下单日期,
Ord_CreateDateTime AS 下单日期查询,
(SELECT COUNT(Ordv_ViceNumber) AS Expr1 FROM dbo.tb_ErpOrderDigital WHERE (Ordv_Number = dbo.tb_ErpOrder.Ord_Number)) AS Ord_ViceOrderCount
FROM 
dbo.tb_ErpOrder LEFT JOIN dbo.tb_ErpOrderDigital  ON dbo.tb_ErpOrder.Ord_Number = tb_ErpOrderDigital.Ordv_Number
left join [dbo].[Vw_OrderProductPickupView] on dbo.tb_ErpOrderDigital.Ordv_ViceNumber=[dbo].[Vw_OrderProductPickupView].OPlist_ViceNumber
left join tempTB_AggregationCustomer on tb_ErpOrder.Ord_Number = GP_OrderNumber
where CONVERT(int,Ord_Type) < 3 
GO